import os
import MySQLdb
import csv
import datetime
import time
import os
import urllib
import shutil
import subprocess
import sys
#
#
#
#
#
#
#
#



weatherFilesLocationRoot = 'www.ksg.eti.pg.gda.pl/prognozy/CSV/poland/'
weatherFilesNames = [ 'SKIN_TEMPERATURE.csv', 'SOIL_TEMPERATURE.csv',
					'VISIBILITY.csv', 'U_WIND_ON_10M.csv',
					'V_WIND_ON_10M.csv', 'SURFACE_REL_HUMID.csv',
					'TOTAL_COLUMN_RAIN.csv', 'TOTAL_COLUMN_SNOW.csv',
					'LOW_CLOUD_FRACTION.csv', 'MID_CLOUD_FRACTION.csv', 'HIGH_CLOUD_FRACTION.csv']

db_host = '188.124.187.145'
db_name = 'inz'
db_user = 'test'
db_pass = '4qLf4yzPKLUL88WB'

ignore_values = [ '9.999e+20' ]

locations = []
windStorage = []


sql_skin_temp_storage = {}
sql_soil_temp_storage = {}
sql_visibility_storage = {}
sql_u_win_10m_storage = {}
sql_v_win_10m_storage = {}
sql_surf_rel_hum_storage = {}
sql_total_col_rain_storage = {}
sql_total_col_snow_storage = {}
sql_low_cloud_fra_storage = {}
sql_mid_cloud_fra_storage = {}
sql_high_cloud_fra_storage = {}

lat_step = 0.0322727272727273
lon_step = 0.0472550991082019
start_lat = 48.210000 
start_lon = 13.283000
rows = 243
columns = 269

weather_insert_file = 'weather_insert.sql'


sqlite_location_id_column_index = 0;
sqlite_location_lat_column_index = 2;
sqlite_location_lon_column_index = 3;

sqlite_location_table_name = 'location'
sqlite_weather_table_name = 'weather'

SKIN_TEMP = 'skin_temp'
SOIL_TEMP = 'soli_temp'
VISIBILITY = 'visibility'
U_WIND_10M = 'u_wind_10m'
V_WIND_10M = 'v_wind_10m'
SURF_REL_HUM = 'surf_rel_hum'
TOTAL_COL_RAIN = 'total_col_rain'
TOTAL_COL_SNOW = 'total_col_snow'
LOW_CL_FRA = 'low_cl_fra'
MID_CL_FRA = 'mid_cl_fra'
HIGH_CL_FRA = 'high_cl_fra'


current_year = time.strftime("%Y")
current_month = time.strftime("%-m")
current_day = time.strftime("%-d")
current_hour = time.strftime("%-H")

web_data_path = "http://www.ksg.eti.pg.gda.pl/prognozy/CSV/poland/";

wget_http_not_found_error_message='HTTP request sent, awaiting response... 404 Not Found'

#====================================================================================================================================================================

def delete_old_data():
	
	year = int(current_year)
	month = int(current_month)
	day = int(current_day)
	hour = int(current_hour)
	
	
	if(os.path.exists(weatherFilesLocationRoot)):
		year_dirs = os.listdir(weatherFilesLocationRoot);
		
		for y in year_dirs:
			year_path = os.path.join(weatherFilesLocationRoot, y)
			if(int(y) < year):
				shutil.rmtree(year_path)
			else:
				month_dirs = os.listdir(year_path)
				for m in month_dirs:
					month_path = os.path.join(year_path, m)
					if(int(m) < month):
						shutil.rmtree(month_path)
					else:
						day_dirs = os.listdir(month_path)
						for d in day_dirs:
							day_path = os.path.join(month_path, d)
							if(int(d) < day): 
								shutil.rmtree(day_path)
							elif(int(d) == day):
								hour_dirs = os.listdir(day_path)
								for h in hour_dirs:
									hour_path = os.path.join(day_path, h)
									
									if(int(h) < hour): 
										shutil.rmtree(hour_path);
										print 'Outdated data was removed: [...]/' + str(y) + '/' + str(m) + '/' + str(d) + '/' + str(h)
							
			

def retrive_data_from_server():
	
	datetime_stamp = datetime.datetime.now()
	new_data_flag = False
	
	for i in range(0, 72):
		
		future_datetime_stamp = datetime_stamp + datetime.timedelta(hours=i)
		
		y = str(future_datetime_stamp.year)
		m = str(future_datetime_stamp.month)
		d = str(future_datetime_stamp.day)
		h = str(future_datetime_stamp.hour)
		
		download_path = os.path.join(weatherFilesLocationRoot, y)
		download_path = os.path.join(download_path, m)
		download_path = os.path.join(download_path, d)
		download_path = os.path.join(download_path, h)
		if(not os.path.exists(download_path)):
			datetime_url_part= str(y) + '/' + str(m) + '/' + str(d) + '/' + str(h)
			url=web_data_path +  datetime_url_part + '/'
			args=['wget', '-r', '-np', url]
			proc = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
			result=proc.stdout.read()
			if not wget_http_not_found_error_message in result:
				print 'Data for: '+datetime_url_part+' has been retrived'
				new_data_flag = True
			else:
				break
			proc.stdout.close()
	return new_data_flag

	
def check_if_location_in_range(location, current_lat, current_lon):
	location_lat = location[sqlite_location_lat_column_index]
	location_lon = location[sqlite_location_lon_column_index]
			
	if((location_lat <= current_lat) and (location_lat > current_lat - lat_step)):
		if((location_lon >= current_lon) and (location_lon < current_lon + lon_step)):
			return True
		else:
			return False
	else:
		return False
		
		
		
	
def insert_new_record_in_proper_storage(location_id, datetime_stamp, value, weatherFile):
	
	if(weatherFile == weatherFilesNames[0]):
		sql_skin_temp_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[1]):
		sql_soil_temp_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[2]):
		sql_visibility_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[3]):
		sql_u_win_10m_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[4]):
		sql_v_win_10m_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[5]):
		sql_surf_rel_hum_storage[int(location_id), datetime_stamp] = value;

	if(weatherFile == weatherFilesNames[6]):
		sql_total_col_rain_storage[int(location_id), datetime_stamp] = value;

	if(weatherFile == weatherFilesNames[7]):
		sql_total_col_snow_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[8]):
		sql_low_cloud_fra_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[9]):
		sql_mid_cloud_fra_storage[int(location_id), datetime_stamp] = value;
		
	if(weatherFile == weatherFilesNames[10]):
		sql_high_cloud_fra_storage[int(location_id), datetime_stamp] = value;

def read_catalogs_data():
	
	
	yearDirs = os.listdir(weatherFilesLocationRoot)
	yearDirs.sort()
	
	datetime_stamp = datetime.datetime.now()
	
	for i in range(0, 49):
		
		future_datetime_stamp = datetime_stamp + datetime.timedelta(hours=i)
		
		y = str(future_datetime_stamp.year)
		m = str(future_datetime_stamp.month)
		d = str(future_datetime_stamp.day)
		h = str(future_datetime_stamp.hour)
		
		files_path = os.path.join(weatherFilesLocationRoot, y)
		files_path = os.path.join(files_path, m)
		files_path = os.path.join(files_path, d)
		files_path = os.path.join(files_path, h)
		
		if(os.path.exists(files_path)):
			print 'Parsing data for: '+y+'/'+m+'/'+d+'/'+h
			weatherFiles = os.listdir(files_path)
			for weatherFile in weatherFiles:
							
											
						
				if(weatherFile in weatherFilesNames):
					weather_file_path = os.path.join(files_path, weatherFile)
					
					
					
					
#					print (y, m, d, h, weatherFile)

					result = csv.reader(open(weather_file_path, 'rb'), delimiter=',', quotechar='|')
					row_counter = rows
					for row in result:
						col_counter = 0
						
						
						for col in row:
							if((col not in ignore_values) and (col != '')):
								
								current_lat = start_lat + row_counter * lat_step
								current_lon = start_lon + col_counter * lon_step
								
								for location in locations:
									if(check_if_location_in_range(location, current_lat, current_lon)):
										storage_datetime = datetime.datetime(int(y), int(m), int(d), int(h), 0, 0)
										insert_new_record_in_proper_storage(location[sqlite_location_id_column_index], storage_datetime, col, weatherFile)
							col_counter += 1
						row_counter -= 1					
#			
		else:
			break	

				


def retrive_locations_from_db():
	
	
	conn = conn = MySQLdb.connect(db_host, db_user, db_pass, db_name)
	c = conn.cursor()
	
	query = 'SELECT * FROM location'
	
	c.execute(query)
	result = c.fetchall();
	
	for row in result:
		locations.append(row)
	
	c.close()
	
	print 'Locations have been retrived'



def clear_weather_conditions_table():
	
	conn = conn = MySQLdb.connect(db_host, db_user, db_pass, db_name)
	c = conn.cursor()
	
	query = 'DELETE FROM weather'
	c.execute(query)
	
	conn.commit()
	
	c.close()
	
	print 'Weather table has been cleared'
	
	
	
def prepare_and_save_insert_file():
	
	print 'Inserts prepare start...'
	
	file = open(weather_insert_file, "w")
	i = 1
	
	for row in sql_skin_temp_storage:
		id_loc = row[0]
		datetime_stamp = row[1]
		
		
		skin_temp = sql_skin_temp_storage[id_loc, datetime_stamp]
		soil_temp = sql_soil_temp_storage[id_loc, datetime_stamp]
		visibility = sql_visibility_storage[id_loc, datetime_stamp]
		u_wind_10m = sql_u_win_10m_storage[id_loc, datetime_stamp]
		v_wind_10m = sql_v_win_10m_storage[id_loc, datetime_stamp]
		total_col_rain = sql_total_col_rain_storage[id_loc, datetime_stamp]
		total_col_snow = sql_total_col_snow_storage[id_loc, datetime_stamp]
		surf_rel_hum = sql_surf_rel_hum_storage[id_loc, datetime_stamp]
		low_cl_fra = sql_low_cloud_fra_storage[id_loc, datetime_stamp]
		mid_cl_fra=sql_mid_cloud_fra_storage[id_loc, datetime_stamp]
		high_cl_fra=sql_high_cloud_fra_storage[id_loc, datetime_stamp]
		
		
		line_text = "INSERT INTO " + sqlite_weather_table_name + " (id, id_loc, datetime_stamp, skin_temp, soil_temp, visibility, u_wind_10m, v_wind_10m, total_col_rain, total_col_snow, surf_rel_hum, low_cloud_fra, mid_cloud_fra, high_cloud_fra) VALUES (" + str(i) + ", " + str(id_loc) + ", '" + str(datetime_stamp) + "', " + str(skin_temp) + ", " + str(soil_temp) + ", " + str(visibility) + ", " + str(u_wind_10m) + ", " + str(v_wind_10m) + ", " + str(total_col_rain) + ", " + str(total_col_snow) + ", " + str(surf_rel_hum) + ", " + str(low_cl_fra) + ", " + str(mid_cl_fra) + ", " + str(high_cl_fra) + ");"
		file.write(line_text + "\n")
		i += 1
		
	file.close()
	
	print 'inserts have been generated'
	
	
def make_insert_into_db():
	
	file = open(weather_insert_file, "r")
	conn = MySQLdb.connect(db_host, db_user, db_pass, db_name)
	c = conn.cursor()
	
	while 1:
		line = file.readline()
		if not line: 
			break
		c.execute(line)
    
	conn.commit()
	c.close() 
	
	print 'Data has been inserted into db'

#==========================================================================================================================================================
delete_old_data()

if(retrive_data_from_server()):
	retrive_locations_from_db()
	read_catalogs_data()
	prepare_and_save_insert_file()
	clear_weather_conditions_table()
	make_insert_into_db()